"""
地址绑定服务
实现设备与地址的绑定、变更、解绑等核心业务逻辑
"""
from datetime import datetime
from decimal import Decimal
from typing import Optional, List, Tuple

from app.crud.address_crud import (
	get_address_by_id, create_address
)
from app.schemas.address_schemas import AddressCreate
from common.base_enums import WorkOrderDeviceType
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger
from common.response import PaginationParams

logger = create_logger("address_binding_service")


class AddressService:
	"""地址绑定服务类"""

	@staticmethod
	async def bind(
			db: DatabaseService,
			device_type: str,
			device_id: int,
			serial_no: str,
			address_id: int,
			address_type: str = "INSTALLATION",
			is_primary: bool = True,
			description: Optional[str] = None,
	) -> int:
		"""
		绑定设备与地址

		Args:
			db: 数据库服务
			device_type: 设备类型(STATION,BOX,RTU,METER)
			device_id: 设备ID
			serial_no: 设备序列号
			address_id: 地址ID
			address_type: 地址类型，默认为INSTALLATION
			is_primary: 是否主地址，默认为True
			description: 地址描述

		Returns:
			设备地址关联记录ID

		Raises:
			BizException: 业务异常
		"""
		try:
			# 1. 验证地址是否存在
			address = await get_address_by_id(db, address_id)
			if not address:
				raise BizException.from_error_code(
					ErrorCode.NOT_FOUND, message=f"地址ID {address_id} 不存在"
				)

			# 2. 获取区县码(前6位)
			region_code = address.region_code[:6] if address.region_code else None

			# 3. 如果设置为主地址，先将其他同类型地址设为非主地址
			if is_primary:
				await AddressService._set_other_addresses_non_primary(
					db, device_type, device_id, address_type
				)

			# 4. 创建设备地址关联记录
			sql = """
                INSERT INTO dev_device_address (
                    device_type, device_id, device_code, address_id, region_code, address_type, 
                    deleted, is_primary, description, created_at
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    address_id = VALUES(address_id),
                    region_code = VALUES(region_code),
                    deleted = 0,
                    is_primary = VALUES(is_primary),
                    description = VALUES(description),
                    ended_at = NULL
            """

			device_address_id = db.insert(sql, (
				device_type, device_id, serial_no, address_id, region_code, address_type,
				False, is_primary, description, datetime.now()
			))

			logger.info(
				"设备地址绑定成功: device_type=%s, device_id=%s, address_id=%s, device_address_id=%s",
				device_type, device_id, address_id, device_address_id
			)

			return device_address_id

		except BizException:
			raise
		except Exception as e:
			logger.exception(
				"设备地址绑定失败: device_type=%s, device_id=%s, address_id=%s",
				device_type, device_id, address_id
			)
			raise BizException.from_error_code(
				ErrorCode.INTERNAL_SERVER_ERROR, message="设备地址绑定失败"
			) from e

	@staticmethod
	async def change_address(
			db: DatabaseService,
			device_type: str,
			device_id: int,
			device_code: str,
			new_address_id: int,
			address_type: str = "INSTALLATION",
			description: Optional[str] = None
	) -> int:
		"""
		变更设备地址(软删除旧记录 + 新增新记录)

		Args:
			db: 数据库服务
			device_type: 设备类型
			device_id: 设备ID
			device_code: 设备编号
			new_address_id: 新地址ID
			address_type: 地址类型
			description: 地址描述

		Returns:
			新的设备地址关联记录ID
		"""
		try:
			# 1. 软删除旧的地址关联记录
			await AddressService._end_old_address_bindings(
				db, device_type, device_id, address_type
			)

			# 2. 创建新的地址关联记录
			new_device_address_id = await AddressService.bind(
				db, device_type, device_id, device_code, new_address_id, address_type,
				is_primary=True, description=description
			)

			logger.info(
				"设备地址变更成功: device_type=%s, device_id=%s, new_address_id=%s, new_device_address_id=%s",
				device_type, device_id, new_address_id, new_device_address_id
			)

			return new_device_address_id

		except BizException as e:
			logger.exception(
				"设备地址变更失败: device_type=%s, device_id=%s, new_address_id=%s",
				device_type, device_id, new_address_id
			)
			raise BizException.from_error_code(
				ErrorCode.INTERNAL_SERVER_ERROR, message="设备地址变更失败"
			) from e

	@staticmethod
	async def unbind(
			db: DatabaseService,
			device_type: str,
			device_id: int,
			address_type: str = "INSTALLATION"
	) -> bool:
		"""
		解绑设备地址

		Args:
			db: 数据库服务
			device_type: 设备类型
			device_id: 设备ID
			address_type: 地址类型

		Returns:
			是否解绑成功
		"""
		try:
			result = await AddressService._end_old_address_bindings(
				db, device_type, device_id, address_type
			)

			logger.info(
				"设备地址解绑成功: device_type=%s, device_id=%s, address_type=%s",
				device_type, device_id, address_type
			)

			return result > 0

		except Exception as e:
			logger.exception(
				"设备地址解绑失败: device_type=%s, device_id=%s, address_type=%s",
				device_type, device_id, address_type
			)
			raise BizException.from_error_code(
				ErrorCode.INTERNAL_SERVER_ERROR, message="设备地址解绑失败"
			) from e

	@staticmethod
	async def get_device_primary_address(
			db: DatabaseService,
			device_type: str,
			device_id: int,
			address_type: str = "INSTALLATION"
	) -> Optional[dict]:
		"""
		获取设备主地址信息

		Args:
			db: 数据库服务
			device_type: 设备类型
			device_id: 设备ID
			address_type: 地址类型

		Returns:
			地址信息字典或None
		"""
		try:
			sql = """
                SELECT da.id as device_address_id, da.address_type, da.is_primary, 
                       da.description, da.region_code,
                       ba.id as address_id, ba.name as address_name, ba.full_name, 
                       ba.longitude, ba.latitude, ba.level, ba.region_code as full_region_code
                FROM dev_device_address da
                INNER JOIN base_region_address ba ON da.address_id = ba.id
                WHERE da.device_type = %s AND da.device_id = %s 
                  AND da.address_type = %s AND da.deleted = 0 AND ba.is_active = 1
                  AND da.is_primary = 1
                ORDER BY da.created_at DESC
                LIMIT 1
            """

			result = db.get_one(sql, (device_type, device_id, address_type))

			if result:
				return {
					"device_address_id": result["device_address_id"],
					"address_type": result["address_type"],
					"is_primary": bool(result["is_primary"]),
					"description": result["description"],
					"region_code": result["region_code"],
					"address_id": result["address_id"],
					"address_name": result["address_name"],
					"full_address": result["full_name"],
					"longitude": result["longitude"],
					"latitude": result["latitude"],
					"level": result["level"],
					"full_region_code": result["full_region_code"]
				}

			return None

		except Exception as e:
			logger.exception(
				"获取设备主地址失败: device_type=%s, device_id=%s, address_type=%s",
				device_type, device_id, address_type
			)
			raise BizException.from_error_code(
				ErrorCode.INTERNAL_SERVER_ERROR, message="获取设备主地址失败"
			) from e

	@staticmethod
	async def auto_create_address_from_gps(
			db: DatabaseService,
			longitude: Decimal,
			latitude: Decimal,
			name: Optional[str] = None
	) -> int:
		"""
		根据GPS坐标自动创建详细地址

		Args:
			db: 数据库服务
			longitude: 经度
			latitude: 纬度
			name: 地址名称，如果为空则使用坐标作为名称

		Returns:
			创建的地址ID

		Note:
			实际项目中应该调用逆地理编码服务获取详细地址信息
			这里简化处理，直接创建level=7的详细地址
		"""
		try:
			# TODO: 调用逆地理编码服务获取详细地址信息
			# 这里简化处理，直接使用坐标创建地址

			if not name:
				name = f"坐标点({longitude},{latitude})"

			full_name = f"自动生成地址,经度:{longitude},纬度:{latitude}"

			# 创建地址记录
			address_payload = AddressCreate(
				parent_id=None,
				name=name,
				full_name=full_name,
				level=7,  # 详细地址级别
				region_code=None,  # TODO: 从逆地理编码获取
				longitude=longitude,
				latitude=latitude,
				source="AUTO_GPS",
				is_active=True
			)

			address_id = await create_address(db, address_payload)

			logger.info(
				"自动创建GPS地址成功: address_id=%s, longitude=%s, latitude=%s",
				address_id, longitude, latitude
			)

			return address_id

		except Exception as e:
			logger.exception(
				"自动创建GPS地址失败: longitude=%s, latitude=%s",
				longitude, latitude
			)
			raise BizException.from_error_code(
				ErrorCode.INTERNAL_SERVER_ERROR, message="自动创建GPS地址失败"
			) from e

	@staticmethod
	async def get_addresses_by_region_prefix(
			db: DatabaseService,
			region_code_prefix: str,
			min_level: int = 4
	) -> List[dict]:
		"""
		根据区域码前缀获取地址列表

		Args:
			db: 数据库服务
			region_code_prefix: 区域码前缀(如前6位区县码)
			min_level: 最小地址级别，默认为4(区县级)

		Returns:
			地址信息列表
		"""
		try:
			sql = """
                SELECT id, parent_id, name, full_name, level, region_code, 
                       longitude, latitude, source, is_active, created_at, updated_at
                FROM base_region_address
                WHERE region_code LIKE %s AND level >= %s AND is_active = 1
                ORDER BY level ASC, id ASC
            """

			results = db.get_all(sql, (f"{region_code_prefix}%", min_level))

			addresses = []
			for result in results:
				addresses.append({
					"id": result["id"],
					"parent_id": result["parent_id"],
					"name": result["name"],
					"full_name": result["full_name"],
					"level": result["level"],
					"region_code": result["region_code"],
					"longitude": result["longitude"],
					"latitude": result["latitude"],
					"source": result["source"],
					"is_active": bool(result["is_active"]),
					"created_at": result["created_at"],
					"updated_at": result["updated_at"]
				})

			return addresses

		except Exception as e:
			logger.exception(
				"根据区域码前缀获取地址列表失败: region_code_prefix=%s, min_level=%s",
				region_code_prefix, min_level
			)
			raise BizException.from_error_code(
				ErrorCode.INTERNAL_SERVER_ERROR, message="获取地址列表失败"
			) from e

	@staticmethod
	async def get_available_addresses_paged(
			db: DatabaseService,
			station_id: int,
			query: PaginationParams,
	) -> Tuple[int, List[dict]]:
		"""
		根据台区主地址的 region_code 前 6 位，分页获取下级地址列表（level>=4）
		:return (total, [address_dict, ...])
		"""
		from app.crud.address_crud import get_addresses_by_region_prefix_paged

		# 1. 取出台区主地址 region_code
		station_primary = await AddressService.get_device_primary_address(
			db, WorkOrderDeviceType.STATION.value, station_id
		)
		if not station_primary or not station_primary["region_code"]:
			raise BizException.from_error_code(
				ErrorCode.NOT_FOUND, message="台区未绑定有效地址，无法获取可选地址"
			)

		region_prefix = station_primary["region_code"][:6]  # 区县码

		# 2. 直接调用 CRUD 分页查询
		total, rows = await get_addresses_by_region_prefix_paged(
			db,
			region_prefix=region_prefix,
			min_level=4,  # 街道/社区/详细地址
			keyword=query.keyword,
			page=query.page,
			size=query.size,
		)
		return total, rows

	# ============ 私有方法 ============

	@staticmethod
	async def _set_other_addresses_non_primary(
			db: DatabaseService,
			device_type: str,
			device_id: int,
			exclude_address_type: str
	) -> int:
		"""将其他地址设为非主地址"""
		sql = """
            UPDATE dev_device_address 
            SET is_primary = 0 
            WHERE device_type = %s AND device_id = %s 
              AND address_type != %s AND deleted = 0
        """
		return db.update(sql, (device_type, device_id, exclude_address_type))

	@staticmethod
	async def _end_old_address_bindings(
			db: DatabaseService,
			device_type: str,
			device_id: int,
			address_type: str
	) -> int:
		"""结束旧的地址绑定记录(软删除)"""
		sql = """
            UPDATE dev_device_address 
            SET deleted = 1, ended_at = %s
            WHERE device_type = %s AND device_id = %s 
              AND address_type = %s AND deleted = 0
        """
		return db.update(sql, (datetime.now(), device_type, device_id, address_type))
